import datetime
from threading import Lock

from flask import Flask, jsonify, request, json
import pymysql
from flask_cors import CORS
app = Flask(__name__)
CORS(app)


connect=pymysql.connect(host='localhost',user='root',passwd='cbq123456',db='JQSP')
#线程锁
mutex=Lock()

#-------------------------------------通用功能------------------------------------------

@app.route('/')
def hello_world():
    return 'fuck yby!'


#登录
@app.route('/login',methods=['POST'])
def login():
    if request.method == 'POST':
        dic = {}
        data = request.get_data()
        # print("data = %s" % data)
        json_data = json.loads(data.decode("utf-8"))
        # print(json_data)
        username = json_data.get("username")
        password = json_data.get("password")
        # print(username)
        cursor = connect.cursor();
        sql = "SELECT * FROM person WHERE user_id = '"+username+"'"
        print(sql)
        result_count = cursor.execute(sql)
        if result_count > 0:
            temp = cursor.fetchone()
            print(password)
            print(temp)
            if password == temp[2]:

                dic['user_id'] = temp[0]
                dic['user_name'] = temp[1]
                dic['user_role'] = temp[3]
                dic['user_dial'] = temp[4]
                dic['user_email'] = temp[5]
                dic['user_wallet'] = temp[6]
                dic['spot_id'] = temp[7]
                print(dic)
                res = {
                    'code':0,
                    'msg':dic
                }
                cursor.close()
                return jsonify(res)
            else:
                res = {
                    'code':'1',
                    'msg':'密码错误'
                }
                return jsonify(res)
        else:
            res = {
                'code': '1',
                'msg': '账号不存在'
            }
            return jsonify(res)


        #允许登录
        #     return jsonify('1')
        # else:
        #     #不允许登录
        #     cursor.close()
        #     return jsonify('0')

#注册
@app.route('/register',methods=['POST'])
def register():
    if request.method == 'POST':
        data = request.get_data()
        # print("data = %s" % data)
        json_data = json.loads(data.decode("utf-8"))
        # print(json_data)
        #用户id，不允许重复，必填
        username = json_data.get("username")
        #密码，必填
        password = json_data.get("password")
        #用户真名
        realname = json_data.get("realname")
        #用户角色，如果没有此字段，默认为0普通用户。
        userrole = json_data.get("userrole")
        #用户电话
        userdial = json_data.get("userdial")
        #用户邮箱
        useremail = json_data.get("useremail")
        #spotid
        spotid = json_data.get("spotid")
        sql = "INSERT INTO person(user_id, pass_word,user_name,user_role,user_dial,user_email) VALUES(%s, %s, %s, %d, %s, %s)" % (
            "'" + username + "'", "'" + password + "'", "'" + realname + "'",int(userrole),
            "'" + userdial + "'", "'" + useremail + "'")
        print(sql)
        cursor = connect.cursor()
        cursor.execute(sql)
        connect.commit()
        res={
            'code':0
        }
    return jsonify(res)

#-------------------------------------用户功能------------------------------------------

#搜索
@app.route('/search',methods=['POST'])
def search():
    if request.method == 'POST':
        data = request.get_data()
        print("data = %s" % data)
        json_data = json.loads(data.decode("utf-8"))
        print(json_data)
        queryString = json_data.get("queryString")
        print(queryString)
        sql = "SELECT * FROM SCENIC_SPOT WHERE spot_name LIKE '%" + queryString + "%';"
        print(sql)
        cursor = connect.cursor();
        result_count = cursor.execute(sql)
        print(result_count)

        if result_count>0:
            temp = cursor.fetchall()
            msg = []
            for item in temp:
                dic = {}
                dic['spot_id'] = item[0]
                dic['spot_name'] = item[1]
                dic['spot_info'] = item[2]
                dic['spot_addr'] = item[3]
                dic['spot_dial'] = item[4]
                msg.append(dic)
            res = {
                'code':0,
                'msg':msg
            }
            print(res)
            return jsonify(res)
        elif result_count==0:
            res = {
                'code':0,
                'msg':[]
            }
            return jsonify(res)
        else:
            res = {
                'code':1,
                'msg':[]
            }
            return jsonify(res)

#按类搜索
@app.route('/searchbyclass',methods=['POST'])
def searchbyclass():
    data = request.get_data()
    print("data = %s" % data)
    json_data = json.loads(data.decode("utf-8"))
    print(json_data)
    type = json_data.get("type")
    sql = "SELECT * FROM SCENIC_SPOT WHERE spot_type LIKE '%"+type+"%';"
    print(sql)
    cursor = connect.cursor();
    result_count = cursor.execute(sql)
    print(result_count)
    if result_count > 0:
        temp = cursor.fetchall()
        msg = []
        for item in temp:
            dic = {}
            dic['spot_id'] = item[0]
            dic['spot_name'] = item[1]
            dic['spot_info'] = item[2]
            dic['spot_addr'] = item[3]
            dic['spot_dial'] = item[4]
            msg.append(dic)
        res = {
            'code': 0,
            'msg': msg
        }
        print(res)
        return jsonify(res)
    elif result_count == 0:
        res = {
            'code': 0,
            'msg': []
        }
        return jsonify(res)
    else:
        res = {
            'code': 1,
            'msg': []
        }
        return jsonify(res)

#按序搜索
@app.route('/searchbydesc',methods=['POST'])
def searchbydesc():
    return jsonify(0)

#获取详情
@app.route('/getdetail',methods=['POST'])
def getdetail():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotid = json_data.get("spotid")
    sql = '''SELECT
                *
            FROM
    	        scenic_spot
            WHERE
    	        spot_id = '%s'
            ''' % (spotid)
    print(sql)
    cursor = connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>0:
        queryresult = cursor.fetchall()
        res = {
            'code':0,
            'msg':{
                'spotid':queryresult[0][0],
                'spotname': queryresult[0][1],
                'spotinfo': queryresult[0][2],
                'spotaddr': queryresult[0][3],
                'spotdial': queryresult[0][4],
                'spottype':queryresult[0][5],
                'spotcapacity':queryresult[0][6]
            }
        }
        return jsonify(res)
    else:
        res = {
            'code': 1,
            'msg': 'no data'
        }
        return jsonify(res)

#获取批次列表
@app.route('/getbatchlist',methods=['POST'])
def getbatchlist():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotid = json_data.get("spotid")
    sql = '''SELECT
                *
            FROM
    	        tour_batch
            WHERE
    	        batch_spotid = '%s'
            ''' % (spotid)
    print(sql)
    cursor=connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>=0:
        msg = []
        for tup in cursor.fetchall():
            dic = {
                'batchid':tup[0],
                'spotid':tup[1],
                'batchdate': tup[2],
                'ticnum': tup[3],
                'resttic': tup[4],
                'ticprice':str(tup[5])
            }
            print(dic)
            msg.append(dic)
        res = {
            'code':0,
            'msg':msg
        }
        return jsonify(res)
    else:
        res = {
            'code':1,
            'msg':[]
        }
        return jsonify(res)

#购票
@app.route('/getticket',methods=['POST'])
def getticket():
    if request.method == 'POST':
        data = request.get_data()
        # print("data = %s" % data)
        json_data = json.loads(data.decode("utf-8"))
        # print(json_data)
        username = json_data.get("username")
        batchid = json_data.get("batchid")
        sql = '''SELECT
	        spot_name,
	        spot_id,
	        batch_date,
	        batch_resttic,
	        batch_price
        FROM
	        scenic_spot,
	        tour_batch
        WHERE
	        spot_id IN (
		        SELECT
			        batch_spotid
		        FROM
			        tour_batch
		        WHERE
			        batch_id = '%s'
	        )
        AND batch_id = '%s' '''%(batchid,batchid)
        print(sql)
        cursor = connect.cursor()
        result_num = cursor.execute(sql)
        if result_num>0:
            tickmsg = cursor.fetchall()
            spotname = tickmsg[0][0]
            spotid = tickmsg[0][1]
            batchdate = tickmsg[0][2]
            resttic = tickmsg[0][3]
            price = tickmsg[0][4]
            if resttic > 0:
                tickitid = batchid + "-" + str(resttic)
                sql = '''
                    INSERT INTO ticket (
	                    tic_id,
	                    tic_batchid,
	                    tic_dealerid,
	                    tic_dealdate
                    )
                    VALUES(
		                '%s',
		                '%s',
		                '%s',
		                '%s'
	                )
                '''%(tickitid,
                     batchid,
                     username,
                     datetime.datetime.now().strftime("%Y-%m-%d"))
                print(sql)
                cursor.execute(sql)
                sql = '''
                    UPDATE tour_batch
                        SET batch_resttic = batch_resttic - 1
                    WHERE
                        batch_id = '%s';
                '''%(batchid)
                print(sql)
                cursor.execute(sql)
                connect.commit()
                res = {
                    'code':0,
                    'msg':'购票成功'
                }
                return res
            else:
                res = {
                    'code': '1',
                    'msg': '票已售完'
                }
                return jsonify(res)
        else:
            res={
                'code':'1',
                'msg':'找不到对应旅行批次'
            }
            return jsonify(res)
        res = {
            'code': '1',
            'msg': '未知错误，响应超时'
        }
    return jsonify(res)

#获得用户订单列表
@app.route('/getorder',methods=['POST'])
def getorder():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    userid = json_data.get("userid")
    sql = '''SELECT
                tic_id,
                tic_dealdate,
                batch_id,
                batch_price,
                batch_date,
                spot_id,
                spot_name
            FROM
    	        ticket,
    	        tour_batch,
    	        scenic_spot
            WHERE
    	        tic_dealerid = '%s'
    	    AND 
    	        tic_batchid = batch_id
    	    AND
    	        batch_spotid = spot_id
            ''' % (userid)
    print(sql)
    cursor=connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>=0:
        msg = []
        for row in cursor.fetchall():
            dic={
                'ticid':row[0],
                'dealdate':row[1],
                'batchid': row[2],
                'price':str(row[3]),
                'tourdate':row[4],
                'spotid':row[5],
                'spotname': row[6],
            }
            msg.append(dic)
        res={
            'code':0,
            'msg':msg
        }
        return jsonify(res)
    else:
        res={
            'code': 1,
            'msg': []
        }
        return jsonify(res)

#用户评论
@app.route('/comment',methods=['POST'])
def comment():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    userid = json_data.get("userid")
    spotid = json_data.get("spotid")
    commenttext = json_data.get("commenttext")
    sql = '''
    INSERT INTO comment (
    cmt_userid,
    cmt_time,
    cmt_spotid,
    cmt_text
    )
    VALUES(
    '%s',
    '%s',
    '%s',
    '%s'
    )
    '''%(userid,str(datetime.datetime.now()),spotid,commenttext)
    print(sql)
    cursor=connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>0:
        res={
            'code':0,
            'msg':'success'
        }
    else:
        res={
            'code':1,
            'msg':'failed'
        }
    connect.commit()
    cursor.close()
    return jsonify(res)

#获取景区评论列表
@app.route('/getcomment',methods=['POST'])
def getcomment():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotid = json_data.get("spotid")
    sql = '''
    SELECT * FROM comment WHERE cmt_spotid = '%s';
    '''%(spotid)
    print(sql)
    cursor=connect.cursor()
    cursor.execute(sql)
    msg=[]
    if cursor.rowcount>=0:
        for row in cursor.fetchall():
            dic={
                'userid':row[0],
                'time': row[1],
                'spotid': row[2],
                'text': row[3]
            }
            msg.append(dic)
        res={
            'code':0,
            'msg':msg
        }
    else:
        res={
            'code': 1,
            'msg': msg
        }
    return jsonify(res)


#用户收藏



#获取景区收藏列表




#-------------------------------------管理员功能------------------------------------------

#获取景区列表
@app.route('/getspotlist',methods=['POST'])
def getspotlist():
    sql = '''SELECT
                *
            FROM
    	        scenic_spot;
            '''
    print(sql)
    cursor = connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>=0:
        msg = []
        for tup in cursor.fetchall():
            dic={
                'spotid': tup[0],
                'spotname': tup[1],
                'spotaddr': tup[3],
                'spotdial': tup[4],
                'spottype': tup[5],
                'spotcapacity': tup[6],
            }
            msg.append(dic)
        res={
            'code':0,
            'msg':msg
        }
        return jsonify(res)
    else:
        res={
            'code':1,
            'msg':[]
        }
        return jsonify(res)

#增添景区
@app.route('/addspot',methods=['POST'])
def addspot():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotname = json_data.get("spotname")
    spotinfo = json_data.get("spotinfo")
    spotaddr = json_data.get("spotaddr")
    spotdial = json_data.get("spotdial")
    spottype = json_data.get("spottype")
    spotcapacity = json_data.get("spotcapacity")
    cursor=connect.cursor()
    sql='''SELECT COUNT(*) FROM scenic_spot'''
    cursor.execute(sql)
    num = cursor.fetchone()
    spotid = 'SP00'+str(num[0]+1)
    sql = '''INSERT INTO scenic_spot(
    spot_id,
    spot_name,
    spot_info,
    spot_addr,
    spot_dial,
    spot_type,
    spot_capacity
    )
    VALUES(
    '%s',
    '%s',
    '%s',
    '%s',
    '%s',
    '%s',
    '%d'
    )
            '''%(spotid,spotname,spotinfo,spotaddr,spotdial,spottype,spotcapacity)
    cursor.execute(sql)
    connect.commit()
    if(cursor.rowcount>0):
        res = {
            'code':0,
            'msg':'success'
        }
    else:
        res = {
            'code': 1,
            'msg': 'failed'
        }
    cursor.close()
    return jsonify(res)

#删除景区
@app.route('/deletespot',methods=['POST'])
def deletespot():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotid = json_data.get("spotid")
    sql = '''
    DELETE FROM scenic_spot WHERE spot_id='%s';
    '''%(spotid)
    cursor=connect.cursor()
    cursor.execute(sql)
    if cursor.rowcount>0:
        res={
            'code':0,
            'msg':'success'
        }
    else:
        res={
            'code':1,
            'msg':'failed'
        }
    connect.commit()
    cursor.close()
    return jsonify(res)

#增加批次
@app.route('/addbatch',methods=['POST'])
def addbatch():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    spotid = json_data.get("spotid")
    date = json_data.get("date")
    ticnum = json_data.get("ticnum")
    price = json_data.get("price")
    batchid=spotid+'-'+date
    cursor=connect.cursor()
    sql = '''INSERT INTO tour_batch(
    batch_id,
    batch_spotid,
    batch_date,
    batch_ticnum,
    batch_resttic,
    batch_price
    )
    VALUES(
    '%s',
    '%s',
    '%s',
    '%d',
    '%s',
    '%d'
    )
            '''%(batchid,spotid,date,ticnum,ticnum,price)
    cursor.execute(sql)
    connect.commit()
    if(cursor.rowcount>0):
        res = {
            'code':0,
            'msg':'success'
        }
    else:
        res = {
            'code': 1,
            'msg': 'failed'
        }
    cursor.close()
    return jsonify(res)



if __name__ == '__main__':
    app.run(host='0.0.0.0',port=5000)
